package com.eyas.business.service.stats;

import com.eyas.business.model.pojo.statsinfo.AdviserParamDTO;
import com.eyas.business.model.pojo.statsinfo.AdviserResultDTO;
import com.eyas.business.model.pojo.statsinfo.IncomeParamDTO;
import com.eyas.business.model.pojo.statsinfo.IncomeResultDTO;
import com.eyas.business.model.pojo.statsinfo.QueryParamDTO;
import com.eyas.business.model.pojo.statsinfo.QueryResultDTO;

import static com.eyas.core.jdbc.utils.CommonJdbcUtils.*;
import com.eyas.core.model.page.PageResult;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @Auther: 王龙龙
 * @Date: 2019/3/2 15:49
 * @Description:
 */
@Service
@Transactional
public class StatsServiceImpl  {
    
	/**按教练统计所教课时
     * @param queryPageParmDTO
     * @param page
     * @param limit
     * @return
     */
    public PageResult<QueryResultDTO> statsRecordByCoach(QueryParamDTO queryPageParmDTO, int page, int limit) {
        StringBuffer sb = new StringBuffer("");
        List list = new ArrayList<Object>();
        sb.append(" select a.coachid,a.coachname, ")
                .append(" (select keyworlds from keyworldsinfo where dcode='COACHLV' and validflag='1' and keycode=a.coachlevel) as coachleveltext, ")
                .append(" sum(case when c.coachflag='1' then 1 else 0 end) as count, ")
                .append(" sum(case when c.coachflag<>'1' then 1 else 0 end) as assistentcount ")
                .append(" from coachinfo a ")
                .append(" left join ( ")
                .append(" select e.coachid,e.coachflag from recordinfo b,recordandcoach e ")
                .append(" where b.recordid=e.recordid and b.status <>'4' ")
                .append(" and exists (SELECT 1 FROM appointmentinfo d WHERE b.recordid=d.recordid AND d.status='2') ");
        if(queryPageParmDTO.getStarttime()!=null) {
            sb.append(" and b.starttime >= ? ");
            list.add(queryPageParmDTO.getStarttime());
        }
        if(queryPageParmDTO.getEndtime()!=null) {
            sb.append(" and b.starttime <= ? ");
            list.add(queryPageParmDTO.getEndtime());
        }
        sb.append(" ) c ")
                .append(" on a.coachid=c.coachid ");
        sb.append(" group by a.coachid,a.coachname,a.coachlevel ");
        /*sb.append(" SELECT r.coachid, c.coachname,k.keyworlds as coachleveltext, COUNT(1) count from recordinfo r LEFT JOIN coachinfo  c   ")
                .append("  on c.coachid=r.coachid  and c.validflag='1' ")
                .append(" left join keyworldsinfo k on c.coachlevel=k.keycode and k.dcode='COACHLV' and k.validflag='1' ")
                .append(" where 1=1 and r.status='3' ");*/

//        sb.append(" GROUP BY r.coachid  ");
        PageResult<QueryResultDTO> pageResult = commonJdbcUtils.queryForPage(sb.toString(),QueryResultDTO.class,page,limit,list);
        return pageResult;
    }
    public PageResult<QueryResultDTO> statsByLevel(QueryParamDTO queryPageParmDTO, int page, int limit) {
        StringBuffer sb = new StringBuffer("");
        List list = new ArrayList<Object>();
        sb.append(" SELECT r.coachid, c.coachname,k.keyworlds as coachleveltext, COUNT(1) count from recordinfo r LEFT JOIN coachinfo  c   ")
                .append("  on c.coachid=r.coachid  and c.validflag='1' ")
                .append(" left join keyworldsinfo k on c.coachlevel=k.keycode and k.dcode='COACHLV' and k.validflag='1' ")
                .append(" where 1=1 and r.status='3' ");
        if(queryPageParmDTO.getStarttime()!=null) {
            sb.append(" and r.starttime >= ? ");
            list.add(queryPageParmDTO.getStarttime());
        }
        if(queryPageParmDTO.getEndtime()!=null) {
            sb.append(" and r.starttime <= ? ");
            list.add(queryPageParmDTO.getEndtime());
        }
        sb.append(" GROUP BY r.coachid  ");
        PageResult<QueryResultDTO> pageResult = commonJdbcUtils.queryForPage(sb.toString(),QueryResultDTO.class,page,limit,list);
        return pageResult;
    }


    /**
     * 课程顾问统计
     * @param paramDTO
     * @param page
     * @param limit
     * @return
     */
    public PageResult<AdviserResultDTO> statsAdviser(AdviserParamDTO paramDTO, int page, int limit) {
        StringBuffer sb = new StringBuffer("");
        List args = new ArrayList();
        sb.append(" select a.adminnid,a.username,count(distinct b.userid) as usercount, ")
                .append(" ifnull(sum(ifnull(c.consumes,0)),0)-ifnull(sum(ifnull(c.consumest,0)),0) as classcount ")
                .append(" from admininfo a, ")
                .append(" userinfo b left join appointmentinfo c ")
                .append(" on b.userid=c.userid and c.status<>'4' ");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        if (paramDTO.getStarttime() != null) {
            sb.append(" and c.operationtime >= ? ");
            args.add(simpleDateFormat.format(paramDTO.getStarttime()));
        }
        if (paramDTO.getEndtime() != null) {
            sb.append(" and c.operationtime <= ? ");
            args.add(simpleDateFormat.format(paramDTO.getEndtime()));
        }
        sb.append(" where a.adminnid=b.addid  ");
        if (StringUtils.hasText(paramDTO.getAdvisername())) {
            sb.append(" and a.username like ? ");
            args.add("%" + paramDTO.getAdvisername() + "%");
        }

        sb.append(" group by a.adminnid,a.username ");
        PageResult<AdviserResultDTO> resultDTOPageResult = commonJdbcUtils.queryForPage(sb.toString(),AdviserResultDTO.class,page,limit,args);
        return resultDTOPageResult;
    }
    /**营收统计
     * @param queryPageParmDTO
     * @param page
     * @param limit
     * @return
     */
    public PageResult<IncomeResultDTO> statsIncome(IncomeParamDTO queryPageParmDTO, int page, int limit) {
        StringBuffer sb = new StringBuffer("");
        List list = new ArrayList<Object>();
        sb.append(" select SUM(a.amount) amount from rechargeinfo  a ")
                .append(" where 1=1  ");
        if(queryPageParmDTO.getStarttime()!=null) {
            sb.append(" and a.addtime >= ? ");
            list.add(queryPageParmDTO.getStarttime());
        }
        if(queryPageParmDTO.getEndtime()!=null) {
            sb.append(" and a.addtime <= ? ");
            list.add(queryPageParmDTO.getEndtime());
        }
        PageResult<IncomeResultDTO> pageResult = commonJdbcUtils.queryForPage(sb.toString(),IncomeResultDTO.class,page,limit,list);
        return pageResult;
    }
}
